package game.gamestorage.texas.db;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

import util.DebugOut;
import util.sql.ConnectionManager;

/**
 * This class handles the communication to the database</br>
 * offers different kinds of returnable datatypes
 * 
 * @author Witthold/Korol
 */
public class SQLHandler {

	private ConnectionManager cm;


	/*
	 * TODO - consider following
	 * Possible improvements for moment of executing query:
	 * Consider ring game gap: insert opponents bets when you get them; else caching? - when best?
	 * Possible improvements for DB:
	 * PreparedStatement: http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/preparedstatement.html
	 * Statement Pooling?
	 * Use of Hash-Indices?
	 */

	/**
	 * Constructor default</br>
	 * initializes ConnectionManager
	 */
	public SQLHandler() {
		cm = new ConnectionManager();
	}


	/**
	 * Constructor with specific database params</br>
	 * probably only need for test cases
	 * 
	 * @param driver which dbc driver
	 * @param databaseUrl -
	 * @param username -
	 * @param password -
	 * @param maxPoolSize - how many db-connection should the ConnectionManager allocate
	 */
	public SQLHandler(String driver, String databaseUrl, String username, String password, int maxPoolSize) {
		cm = new ConnectionManager(driver, databaseUrl, username, password, maxPoolSize);
	}


	/**
	 * sqlSet:<br>
	 * INSERT, UPDATE, DELETE<br>
	 * 
	 * @param sql query
	 * @return true, if writing to db succeeded
	 */
	protected boolean sqlSet(String sql) {

		try {
			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			int rows = stmt.executeUpdate(sql);

			stmt.close();
			cm.returnConnectionToPool(con);

			if (rows != -1) {
				DebugOut.showVerboseSqlHandler("rows: " + rows);
				return true;
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return false;
	}


	/**
	 * sqlSetReturnIDs:<br>
	 * INSERT, UPDATE, DELETE<br>
	 * 
	 * @param sql query
	 * @return int[] of autogenerated IDs
	 */
	protected int[] sqlSetReturnIDs(String sql) {

		int[] ids = null;

		try {
			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			int rows = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

			if (rows != -1) {
				ResultSet rs = stmt.getGeneratedKeys();
				if (rs.next()) {
					ResultSetMetaData rsmd = rs.getMetaData();
					int colCount = rsmd.getColumnCount();
					int[] idstmp = new int[colCount];
					do {
						for (int i = 1; i <= colCount; i++) {
							//							String key = rs.getString(i);
							int id = rs.getInt(i);
							//							DebugOut.showVerboseSqlHandler("String key " + i + "is " + key);
							DebugOut.showVerboseSqlHandler("int id " + i + "is " + id);
							idstmp[i - 1] = id;
						}
					} while (rs.next());
					ids = idstmp;
				} else {
					DebugOut.showVerboseSqlHandler("There are no generated ids.");
				}
			}

			stmt.close();
			cm.returnConnectionToPool(con);

			return ids;

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}


	/**
	 * sqlSetReturnID:<br>
	 * INSERT, UPDATE, DELETE<br>
	 * 
	 * @param sql query
	 * @return int of autogenerated ID
	 */
	protected int sqlSetReturnID(String sql) {

		int id = -11;

		try {
			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			int rows = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

			if (rows != -1) {
				ResultSet rs = stmt.getGeneratedKeys();
				if (rs.next()) {
					id = rs.getInt(1);
					//					DebugOut.showVerboseSqlHandler("int id " + id);
				} else {
					DebugOut.showVerboseSqlHandler("There is no generated id.");
				}
			}

			stmt.close();
			cm.returnConnectionToPool(con);

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return id;
	}


	/**
	 * sqlGetResultSet:<br>
	 * SELECT<br>
	 * 
	 * @param sql query
	 * @return ResultSet
	 */
	public ResultSet sqlGetResultSet(String sql) {

		try {

			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			ResultSet rs = stmt.executeQuery(sql);

			if (rs.next()) {
				DebugOut.showVerboseSqlHandler("rs.getString(1): " + rs.getString(1));
			} else {
				DebugOut.showVerboseSqlHandler("sqlGetResultSet: ResultSet rs is empty");
			}

			stmt.close();
			cm.returnConnectionToPool(con);

			return rs;
			// consider rs.close()	// probably already closed by stmt.close()
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}


	/**
	 * sqlGetHashMapStringInt:<br>
	 * SELECT<br>
	 * 
	 * @param sql query
	 * @return HashMap
	 */
	public HashMap<String, Integer> sqlGetHashMapStringInt(String sql) {

		try {

			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			HashMap<String, Integer> hm = new HashMap<String, Integer>(); 

			ResultSet rs = stmt.executeQuery(sql);

			if (rs.next()) {
				ResultSetMetaData rsmd = rs.getMetaData();
				int colCount = rsmd.getColumnCount();
				do {
					String key = "";
					int value = 0;

					for (int i = 1; i <= colCount; i++) {
						if (i == 1) {
							key = rs.getString(i);
							//							DebugOut.showVerboseSqlHandler("String key " + i + ": " + key);
						} else {
							value = rs.getInt(i);
							//							DebugOut.showVerboseSqlHandler("int value " + i + ": " + value);
						}
					}
					hm.put(key, value); // put pair to HashMap
				} while (rs.next());
				//			ids = idstmp;
			} else {
				DebugOut.showVerboseSqlHandler("All players are NEW!.");
			}

			stmt.close();
			cm.returnConnectionToPool(con);

			return hm;
			// consider rs.close()	// probably already closed by stmt.close()
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}


	/**
	 * sqlGetHashMapStringInt:<br>
	 * SELECT<br>
	 * 
	 * @param sql query
	 * @return HashMap
	 */
	public HashMap<Integer, String> sqlGetHashMapIntString(String sql) {

		try {

			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			HashMap<Integer, String> hm = new HashMap<Integer, String>();

			ResultSet rs = stmt.executeQuery(sql);

			if (rs.next()) {
				ResultSetMetaData rsmd = rs.getMetaData();
				int colCount = rsmd.getColumnCount();
				//				int[] idstmp = new int[colCount];
				do {
					int key = 0;
					String value = "";

					for (int i = 1; i <= colCount; i++) {
						if (i == 1) {
							key = rs.getInt(i);
							//							DebugOut.showVerboseSqlHandler("String key " + i + ": " + key);
						} else {
							value = rs.getString(i);
							//							DebugOut.showVerboseSqlHandler("int value " + i + ": " + value);
						}
					}
					hm.put(key, value); // put pair to HashMap
				} while (rs.next());
			} else {
				DebugOut.showVerboseSqlHandler("No data found!.");
			}

			stmt.close();
			cm.returnConnectionToPool(con);

			return hm;
			// consider rs.close()	// probably already closed by stmt.close()
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}


	/**
	 * sqlGetInt:<br>
	 * SELECT<br>
	 * 
	 * @param sql query
	 * @return int result
	 */
	public int sqlGetInt(String sql) {

		int val = -11; // publish this error code

		try {
			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				val = rs.getInt(1);
				//				DebugOut.showVerboseSqlHandler("sqlGetInt - val: " + val);
			} else {
				DebugOut.showVerboseSqlHandler("sqlGetInt: no value to get");
			}
			stmt.close();
			cm.returnConnectionToPool(con);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return val;
	}


	/**
	 * sqlGetIntArrayOnePerRow:<br>
	 * SELECT<br>
	 * 
	 * @param sql query
	 * @return int[]: length = numRows
	 */
	public int[] sqlGetIntArrayOnePerRow(String sql) {

		ArrayList<Integer> val = new ArrayList<Integer>();

		try {
			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			ResultSet rs = stmt.executeQuery(sql);
			int i = 0;
			if (rs.next()) {
				do {
					val.add(rs.getInt(1));
					//					DebugOut.showVerboseSqlHandler("sqlGetIntArray2D - val_" + (i) + ": " + val.get(i));
					i++;
				} while (rs.next());
			} else {
				DebugOut.showVerboseSqlHandler("sqlGetIntArrayOnePerRow: no value to get");
			}
			stmt.close();
			cm.returnConnectionToPool(con);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}

		int[] v = new int[val.size()];
		for (int i = 0; i < v.length; i++) {
			v[i] = val.get(i);
		}

		return v;
	}


	/**
	 * sqlGetIntArrayOnePerRow:<br>
	 * SELECT<br>
	 * 
	 * @param sql query
	 * @return int[]: length = numRows
	 */
	public long[] sqlGetLongArrayOnePerRow(String sql) {

		ArrayList<Long> val = new ArrayList<Long>();

		try {
			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			ResultSet rs = stmt.executeQuery(sql);
			int i = 0;
			if (rs.next()) {
				do {
					val.add(rs.getLong(1));
					//					DebugOut.showVerboseSqlHandler("sqlGetLongArrayOnePerRow - val_" + (i) + ": " + val.get(i));
					i++;
				} while (rs.next());
			} else {
				DebugOut.showVerboseSqlHandler("sqlGetLongArrayOnePerRow: no value to get");
			}
			stmt.close();
			cm.returnConnectionToPool(con);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}

		long[] v = new long[val.size()];
		for (int i = 0; i < v.length; i++) {
			v[i] = val.get(i);
		}

		return v;
	}


	/**
	 * sqlGetIntArrayMultiPerRow:<br>
	 * SELECT<br>
	 * 
	 * @param sql query
	 * @param numCols length
	 * @return int[][]: lengths = numRows, numCols
	 */
	public int[][] sqlGetIntArrayMultiPerRow(String sql, int numCols) {

		ArrayList<int[]> val = new ArrayList<int[]>();

		try {
			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			ResultSet rs = stmt.executeQuery(sql);
			int i = 0;
			if (rs.next()) {
				do {
					int[] arr = new int[numCols];
					for (int j = 0; j < arr.length; j++) {
						arr[j] = rs.getInt(j + 1);
						//						DebugOut.showVerboseSqlHandler("sqlGetIntArrayMultiPerRow - arr_" + (j) + ": " + arr[j]);
					}
					val.add(arr);
					i++;
				} while (rs.next());
			} else {
				DebugOut.showVerboseSqlHandler("sqlGetIntArrayMultiPerRow: no value to get");
			}
			stmt.close();
			cm.returnConnectionToPool(con);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}

		int[][] arr2 = new int[val.size()][numCols];
		for (int i = 0; i < arr2.length; i++) {
			arr2[i] = val.get(i);
		}

		return arr2;
	}


	/**
	 * sqlGetIntArrayListMultiPerRow:<br>
	 * SELECT<br>
	 * 
	 * @param sql query
	 * @param numCols length
	 * @return ArrayList<int[]> size = numRows with int[numCols]
	 */
	public ArrayList<int[]> sqlGetIntArrayListMultiPerRow(String sql, int numCols) {

		ArrayList<int[]> val = new ArrayList<int[]>();

		try {
			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			ResultSet rs = stmt.executeQuery(sql);
			int i = 0;
			if (rs.next()) {
				do {
					int[] arr = new int[numCols];
					for (int j = 0; j < arr.length; j++) {
						arr[j] = rs.getInt(j + 1);
						//						DebugOut.showVerboseSqlHandler("sqlGetIntArrayListMultiPerRow - arr_" + (j) + ": " + arr[j]);
					}
					val.add(arr);
					i++;
				} while (rs.next());
			} else {
				DebugOut.showVerboseSqlHandler("sqlGetIntArrayListMultiPerRow: no value to get");
			}
			stmt.close();
			cm.returnConnectionToPool(con);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}

		return val;
	}


	/**
	 * sqlGetLongArrayListMultiPerRow:<br>
	 * SELECT<br>
	 * 
	 * @param sql query
	 * @param numCols length
	 * @return ArrayList<long[]> size = numRows with int[numCols]
	 */
	public ArrayList<long[]> sqlGetLongArrayListMultiPerRow(String sql, int numCols) {

		//		int[] arr = new int[numCols];
		ArrayList<long[]> val = new ArrayList<long[]>();

		try {
			Connection con = cm.getConnectionFromPool();
			Statement stmt = getStatement(con);

			ResultSet rs = stmt.executeQuery(sql);
			int i = 0;
			if (rs.next()) {
				do {
					long[] arr = new long[numCols];
					for (int j = 0; j < arr.length; j++) {
						//						arr[j] = rs.getInt(j + 1);
						arr[j] = rs.getLong(j + 1);
						//						DebugOut.showVerboseSqlHandler("sqlGetIntArrayListMultiPerRow - arr_" + (j) + ": " + arr[j]);
					}
					val.add(arr);
					i++;
				} while (rs.next());
			} else {
				DebugOut.showVerboseSqlHandler("sqlGetIntArrayListMultiPerRow: no value to get");
			}
			stmt.close();
			cm.returnConnectionToPool(con);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		}

		return val;
	}


	/**
	 * getStatement:<br>
	 * 
	 * @param con
	 * @return Statement for Connection
	 */
	private Statement getStatement(Connection con) {

		try {
			Statement stmt = con.createStatement();
			return stmt;
		} catch (SQLException e2) {
			System.err.println(e2.getMessage());
			e2.printStackTrace();
		}
		System.err.println("NO Statement");
		return null;
	}


	/**
	 * @return true, if there is at least one connection to the db
	 */
	public boolean connectionSuccess() {

		return cm.connectionSuccess();
	}

}
